﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using CinemaBookingProject.DBLayer;
using System.Data;
using System.Threading;
using System.Configuration;

public class DBMovie
{

    static internal SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Balder"].ConnectionString);// new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dmaa0912_E8;Persist Security Info=True;User ID=dmaa0912_E8;Password=MaaGodt");

    private static object obj = new object();
    /// <summary>
    /// Returns a list of movie objects from the database.
    /// </summary>
    /// <param name="title">The title of the movie.</param>
    /// <returns>List of movies</returns>
    public List<Movie> Read(string title)
    {
        SqlDataReader rdr = null;
        List<Movie> movies = new List<Movie>();
        string query = "SELECT * from Movie where Title like @title;";
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(query, Connection);

            cmd.Parameters.Add("@title", SqlDbType.VarChar).Value = "%" + title + "%";

            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Movie mov = new Movie((string)rdr["Title"], (string)rdr["MovieDescription"], (double)rdr["Price"], (bool)rdr["Is3D"], (int)rdr["MovieLength"], (int)rdr["Commercial"], (int)rdr["Trailer"], (string)rdr["MovieImage"]);
                mov.Id = (int)rdr["id"];
                movies.Add(mov);

            }

        }
        finally
        {
            //close reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close connection
            if (Connection != null)
            {
                Connection.Close();
            }
        }
        return movies;
    }
    /// <summary>
    /// Returns a Movie object from the database.
    /// </summary>
    /// <param name="id">The id of the movie.</param>
    /// <returns>Movie object</returns>
    public Movie Read(int id)
    {
        SqlDataReader rdr = null;
        Movie movie = null;
        string query = "SELECT * from Movie where ID = @id;";
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(query, Connection);

            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                movie = new Movie((string)rdr["Title"], (string)rdr["MovieDescription"], (double)rdr["Price"], (bool)rdr["Is3D"], (int)rdr["MovieLength"], (int)rdr["Commercial"], (int)rdr["Trailer"], (string)rdr["MovieImage"]);
                movie.Id = (int)rdr["ID"];
            }

        }
        finally
        {
            //close reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // close connection
            if (Connection != null)
            {
                Connection.Close();
            }
        }
        return movie;
    }
    /// <summary>
    /// Returns a list of all movie objects from the database.
    /// </summary>
    /// <returns>List of movies</returns>
    public List<Movie> ReadAll()
    {
        SqlDataReader rdr = null;
        List<Movie> movies = new List<Movie>();
        string query = "SELECT * from Movie;";
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(query , Connection);

            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                movies.Add(new Movie((string)rdr["Title"], (string)rdr["MovieDescription"], (double)rdr["Price"], (bool)rdr["Is3D"], (int)rdr["MovieLength"], (int)rdr["Commercial"], (int)rdr["Trailer"], (string)rdr["MovieImage"]));
            } 
            
        }
        finally
        {
         
            if (rdr != null)
            {
                rdr.Close();
            }

            
            if (Connection != null)
            {
                Connection.Close();
            }
        }
        return movies;  
    }
    /// <summary>
    /// Inserts a movie object into the database
    /// </summary>
    /// <param name="movie">The Movie object.</param>
    /// <returns>+1 or -1</returns>
    public int Insert(Movie movie)
    {
        int succeed = 0;

         bool exits = DoesExits(movie);
         if (!exits)
         {

             string query = "INSERT INTO Movie(Title, MovieDescription, Price, Is3D, MovieLength, Commercial, Trailer, MovieImage) VALUES (@title, @description, @price, @is3d, @length, @commercials, @trailer, @image);";

             try
             {
                 Monitor.Enter(obj);

                 SqlCommand cmd = new SqlCommand(query, Connection);
                 cmd.Parameters.Add("@title", SqlDbType.Char).Value = movie.Title;
                 cmd.Parameters.Add("@description", SqlDbType.Char).Value = movie.Description;
                 cmd.Parameters.Add("@price", SqlDbType.Float).Value = movie.Price;
                 cmd.Parameters.Add("@is3d", SqlDbType.Bit).Value = movie.Is3d;
                 cmd.Parameters.Add("@Length", SqlDbType.Int).Value = movie.Length;
                 cmd.Parameters.Add("@commercials", SqlDbType.Int).Value = movie.Commercials;
                 cmd.Parameters.Add("@trailer", SqlDbType.Int).Value = movie.Trailer;
                 cmd.Parameters.Add("@image", SqlDbType.Char).Value = movie.Image;

                 Connection.Open();
                 succeed = cmd.ExecuteNonQuery();

             }
             catch (SqlException)
             {
                 succeed = -1;
             }
             catch (Exception)
             {

                 throw;
             }
             finally
             {
                 Connection.Close();
                 Monitor.Exit(obj);
             }
         }
        return succeed;
    }
    /// <summary>
    /// Update a movie object into the database based on title
    /// </summary>
    /// <param name="movie">The Movie object.</param>
    /// <returns>+1 or -1</returns>
    public int Update(Movie movie)
    {
        int succeed = 0;

        string query = "UPDATE Movie SET MovieDescription = @description, Price =  @price, Is3D = @is3d, MovieLength = @length, Commercial = @commercials, Trailer = @trailer, MovieImage = @image where Title = @title;";
        try
        {
            Monitor.Enter(obj);

            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@title", SqlDbType.Char).Value = movie.Title;
            cmd.Parameters.Add("@description", SqlDbType.Char).Value = movie.Description;
            cmd.Parameters.Add("@price", SqlDbType.Float).Value = movie.Price;
            cmd.Parameters.Add("@is3d", SqlDbType.Bit).Value = movie.Is3d;
            cmd.Parameters.Add("@Length", SqlDbType.Int).Value = movie.Length;
            cmd.Parameters.Add("@commercials", SqlDbType.Int).Value = movie.Commercials;
            cmd.Parameters.Add("@trailer", SqlDbType.Int).Value = movie.Trailer;
            cmd.Parameters.Add("@image", SqlDbType.Char).Value = movie.Image;

            Connection.Open();
            succeed = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            succeed = -1;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Connection.Close();
            Monitor.Exit(obj);
        }
        return succeed;
    }
    /// <summary>
    /// Delete a movie object from the database based on id
    /// </summary>
    /// <param name="id">The id of the movie.</param>
    /// <returns>+1 or -1</returns>
    public int Delete(int id)
    {
        string query = "delete from movie where ID = @id;";
        int succeed = 0;

        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            succeed = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            succeed = -1;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Connection.Close();
        }

        return succeed;
    }
    /// <summary>
    /// Checks if the object alredy exits
    /// </summary>
    /// <param name="movie">The Movie object.</param>
    /// <returns>true or false</returns>
    public bool DoesExits(Movie movie)
    {
        bool exits = false;

        List<Movie> check = ReadAll();

        foreach (Movie item in check)
        {
            if (item.Title.Equals(movie.Title))
            {
                exits = true;
            }
        }
        
        return exits;
    }
}

